﻿/*
 *	Copyright (c) 2009 Queensland University of Technology. All rights reserved.
 *	The QUT Bioinformatics Collection is open source software released under the 
 *	Microsoft Public License (Ms-PL): http://www.microsoft.com/opensource/licenses.mspx.
 */
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows;
using QUT.Bio.Util;

namespace DBLoader {
	public class DataLayer {
		private string databaseName = String.Empty;
		private string importOrganismQuery;
		private string importGeneQuery;
		private string importHitQuery;
		private string getHitCountQuery;
		private string getTranslationsQuery;
		private string listAvailableGenomesQuery;
		private string removeHitsQuery;
		private ILogWriter logWriter;

		public DataLayer ( ILogWriter logWriter ) {
			this.logWriter = logWriter;
		}

		public string DatabaseName {
			get {
				return databaseName;
			}
			set {
				databaseName = value;
				SetQueries();
			}
		}

		#region Method: SetQueries
		/// <summary>
		/// Update the query strings to access the currently selected SilverMap database.
		/// </summary>

		private void SetQueries () {
			FormatQuery( "InsertChromosome", out importOrganismQuery );
			FormatQuery( "InsertGene", out importGeneQuery );
			FormatQuery( "InsertHit", out importHitQuery );
			FormatQuery( "GetHitCount", out getHitCountQuery );
			FormatQuery( "GetTranslations", out getTranslationsQuery );
			FormatQuery( "ListAvailableGenomes", out listAvailableGenomesQuery );
			FormatQuery( "RemoveHits", out removeHitsQuery );
		}
		#endregion

		#region Method: FormatQuery
		/// <summary>
		/// Formats a SQL query to use the nominated database.
		/// </summary>
		/// <param name="queryName"></param>
		/// <param name="query"></param>

		private void FormatQuery ( string queryName, out string query ) {
			query = string.Format( GetResource( queryName + ".sql" ), databaseName );
		}
		#endregion

		#region Method: Sql
		/// <summary>
		/// Establishes a connection to the SQL data source (which I tacitly assume to be an instance of SQL
		/// Server or SQL Server Express Edition), and execute action specified by the caller.
		/// <para>As a side effect, error messages are displayed in the application log.</para>
		/// </summary>
		/// <param name="action">A delegate that does the required action.</param>

		public void Sql ( Action<SqlConnection> action ) {
			try {
				using ( SqlConnection connection = new SqlConnection( Configuration.Args["DataConnection"] ) ) {
					connection.Open();
					action( connection );
					connection.Close();
				}
			}
			catch ( Exception e_ ) {
				logWriter.WriteLine( "Database error:" );
				logWriter.WriteLine( e_.Message );
			}
		}
		#endregion

		#region Method: ResourceUri
		/// <summary>
		/// Constructs a uri for a resource file located in the "Resources" folder.
		/// </summary>
		/// <param name="fileName">the name of the resource file.</param>
		/// <returns></returns>

		private Uri ResourceUri ( string fileName ) {
			string assemblyName = GetType().Assembly.FullName;
			string shortName = assemblyName.Substring( 0, assemblyName.IndexOf( ',' ) );
			string uriString = String.Format( "{0};component/Resources/{1}", shortName, fileName );
			return new Uri( uriString, UriKind.Relative );
		}
		#endregion

		#region Method: GetResource
		/// <summary>
		/// Loads the contents of a resource file into a string.
		/// </summary>
		/// <param name="fileName">the name of the resource to be loaded.</param>
		/// <returns></returns>

		private string GetResource ( string fileName ) {
			Uri uri = ResourceUri( fileName );

			using ( StreamReader r = new StreamReader( App.GetResourceStream( uri ).Stream ) ) {
				return r.ReadToEnd();
			}
		}
		#endregion

		#region Method: CreateDatabase.
		/// <summary>
		/// Creates a SilverMap database having the specified name.
		/// </summary>
		/// <param name="fileName">The database name.</param>

		public void CreateDatabase ( string fileName ) {
			string datafile = fileName;
			string logfile = datafile.Replace( ".mdf", "_log.ldf" );
			databaseName = new FileInfo( datafile ).Name.Replace( ".mdf", "" );
			string queryFormatString = GetResource( "CreateSilverMapDatabase.sql" );
			string createDatabaseQuery = String.Format( queryFormatString, databaseName, datafile, logfile );

			Sql( delegate( SqlConnection connection ) {
				foreach ( string sqlStatement in createDatabaseQuery.Split( new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries ) ) {
					SqlCommand command = connection.CreateCommand();
					command.CommandText = sqlStatement;
					command.ExecuteNonQuery();
				}
				SetQueries();
			} );
		}
		#endregion

		#region Method: ImportGene
		/// <summary>
		/// Tries to add a gene to the database, returning 1 if the gene was added or 0 otherwise.
		/// </summary>
		/// <param name="gene"></param>
		/// <returns></returns>

		public int ImportGene ( Gene gene ) {
			int result = 0;

			Sql( delegate( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.CommandText = importGeneQuery;
				command.Parameters.Add( new SqlParameter( "@AccessionVersion", gene.AccessionVersion ) );
				command.Parameters.Add( new SqlParameter( "@GeneId", gene.GeneId ) );
				command.Parameters.Add( new SqlParameter( "@GenbankId", gene.GenbankId ) );
				command.Parameters.Add( new SqlParameter( "@LocusTag", gene.LocusTag ) );
				command.Parameters.Add( new SqlParameter( "@Symbol", gene.Symbol ) );
				command.Parameters.Add( new SqlParameter( "@Cog", gene.Cog ) );
				command.Parameters.Add( new SqlParameter( "@Translation", gene.Translation ) );
				command.Parameters.Add( new SqlParameter( "@Product", gene.Product ) );
				command.Parameters.Add( new SqlParameter( "@ProteinId", gene.ProteinId ) );
				command.Parameters.Add( new SqlParameter( "@Note", gene.Note ) );
				result += (int) command.ExecuteScalar();
			} );

			return result;
		}
		#endregion

		#region Method: ImportOrganism( chromosome )
		/// <summary>
		/// Attempts to add a genome to the SilverMap database.
		/// </summary>
		/// <param name="chromosome">A genome description.</param>

		public void ImportOrganism ( GenomeDescriptor chromosome ) {
			Sql( delegate( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.Parameters.Add( new SqlParameter( "@AccessionVersion", chromosome.AccessionVersion.Replace( ", complete genome", "" ) ) );
				command.Parameters.Add( new SqlParameter( "@Organism", chromosome.Definition ) );
				command.CommandText = importOrganismQuery;

				int result = (int) command.ExecuteScalar();

				logWriter.WriteLine( "{1} {2}added to the Organism table.",
					chromosome.AccessionVersion,
					chromosome.Definition,
					result == 0 ? "has already been " : ""
				);
			} );
		}
		#endregion

		#region Method: GetTranslations
		/// <summary>
		/// Pulls out the translation of each gene in a specified genome.
		/// </summary>
		/// <param name="accessionVersion">The accession-version id of the required genome.</param>
		/// <param name="processTranslation">A method that is called to process each data record that comes back.</param>

		public void GetTranslations ( string accessionVersion, Action<decimal, string> processTranslation ) {
			Sql( delegate( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.Parameters.Add( new SqlParameter( "@AccessionVersion", accessionVersion ) );
				command.CommandText = getTranslationsQuery;
				using ( SqlDataReader reader = command.ExecuteReader() ) {
					while ( reader.Read() ) {
						decimal id = (decimal) reader["Id"];
						string translation = (string) reader["Translation"];
						processTranslation( id, translation );
					}

					reader.Close();
				}
			} );
		}
		#endregion

		#region Method: ListAvailableGenomes.
		/// <summary>
		/// Lists the available genomes in the database, calling a suplied method to process each record.
		/// </summary>
		/// <param name="processGenome">A method that will be called to process each record.</param>

		public void ListAvailableGenomes ( Action<string, string, int> processGenome ) {
			Sql( delegate( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.CommandText = listAvailableGenomesQuery;
				using ( SqlDataReader reader = command.ExecuteReader() ) {
					while ( reader.Read() ) {
						string accessionVersion = (string) reader["AccessionVersion"];
						string definition = (string) reader["Definition"];
						int geneCount = (int) reader["geneCount"];
						processGenome( accessionVersion, definition, geneCount );
					}

					reader.Close();
				}
			} );
		}
		#endregion

		#region Method: ListSilverMapDatabases
		/// <summary>
		/// Gets a list of databases that look like silverMap databases.
		/// This is done by looking for 3 tables: Organism, Gene and Hits.
		/// <para>A small amount of information about matching databases is passed to the supplied delegate for action.</para>
		/// </summary>
		/// <param name="processDatabase">A delegate that will process each database.</param>

		public void ListSilverMapDatabases ( Action<string, int> processDatabase ) {
			// Apology in advance. Slimy code.
			var self = this;

			Sql( delegate( SqlConnection c ) {
				SqlCommand command = c.CreateCommand();
				command.CommandText = "select name from sys.databases where state_desc = 'ONLINE'";

				using ( SqlDataReader r = command.ExecuteReader() ) {
					while ( r.Read() ) {
						string tableName = (string) r[0];

						if ( "|master|tempdb|model|msdb|LogFile|".IndexOf( tableName ) < 0 ) {
							self.Sql( delegate( SqlConnection connection2 ) {
								SqlCommand c2 = connection2.CreateCommand();

								c2.CommandText = String.Format(
									@"use {0} select COUNT(*) from sysobjects where name in ( 'Organism', 'Gene', 'Hits' )",
									tableName
								);

								if ( (int) c2.ExecuteScalar() == 3 ) {
									c2.CommandText = String.Format(
										@"use {0} select COUNT(*) from Organism",
										tableName
									);

									processDatabase( tableName, (int) c2.ExecuteScalar() );
								}
							} );
						}
					}

					r.Close();
				}

			} );
		}
		#endregion

		#region Method: GetHitCount

		/// <summary>
		/// Returns the number of hits between two genomes (one-way).
		/// </summary>
		/// <param name="queryAccession"></param>
		/// <param name="hitAccession"></param>
		/// <returns></returns>

		public int GetHitCount ( string queryAccession, string hitAccession ) {
			int result = 0;

			Sql( delegate( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.Parameters.Add( new SqlParameter( "@QueryAccession", queryAccession ) );
				command.Parameters.Add( new SqlParameter( "@HitAccession", hitAccession ) );
				command.CommandText = getHitCountQuery;

				try {
					result = (int) command.ExecuteScalar();
				}
				catch ( Exception ex ) {
					logWriter.WriteLine( "Error getting hit count for {0} and {1}: {2}",
						queryAccession, hitAccession, ex.Message
					);
				}
			} );

			return result;
		}
		#endregion

		#region Method: ImportHit
		/// <summary>
		/// Imports a blast hit.
		/// </summary>
		/// <param name="hit"></param>

		public void ImportHit ( Hit hit ) {
			Sql( delegate( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.CommandText = importHitQuery;
				command.Parameters.Add( new SqlParameter( "@AlignmentLength", hit.AlignmentLength ) );
				command.Parameters.Add( new SqlParameter( "@BitScore", hit.BitScore ) );
				command.Parameters.Add( new SqlParameter( "@EValue", hit.EValue ) );
				command.Parameters.Add( new SqlParameter( "@HitFrame", hit.HitFrame ) );
				command.Parameters.Add( new SqlParameter( "@HitFrom", hit.HitFrom ) );
				command.Parameters.Add( new SqlParameter( "@HitGaps", hit.HitGaps ) );
				command.Parameters.Add( new SqlParameter( "@HitId", hit.HitId ) );
				command.Parameters.Add( new SqlParameter( "@HitLength", hit.HitLength ) );
				command.Parameters.Add( new SqlParameter( "@HitSequence", hit.HitSequence ) );
				command.Parameters.Add( new SqlParameter( "@HitTo", hit.HitTo ) );
				command.Parameters.Add( new SqlParameter( "@Identity", hit.Identity ) );
				command.Parameters.Add( new SqlParameter( "@Midline", hit.Midline ) );
				command.Parameters.Add( new SqlParameter( "@Positive", hit.Positive ) );
				command.Parameters.Add( new SqlParameter( "@QueryFrame", hit.QueryFrame ) );
				command.Parameters.Add( new SqlParameter( "@QueryFrom", hit.QueryFrom ) );
				command.Parameters.Add( new SqlParameter( "@QueryId", hit.QueryId ) );
				command.Parameters.Add( new SqlParameter( "@QueryLength", hit.QueryLength ) );
				command.Parameters.Add( new SqlParameter( "@QuerySequence", hit.QuerySequence ) );
				command.Parameters.Add( new SqlParameter( "@QueryTo", hit.QueryTo ) );
				command.Parameters.Add( new SqlParameter( "@Rank", hit.Rank ) );
				command.Parameters.Add( new SqlParameter( "@Score", hit.Score ) );
				command.ExecuteNonQuery();
			} );
		} 
		#endregion
		
		#region Method: RemoveHits
		
		/// <summary>
		/// A blunt instrument: removes all hits related to genes from the specified genome.
		/// </summary>
		/// <param name="accessionVersion">The accession-version id of the genome to purge.</param>
		
		public int RemoveHits( string accessionVersion ) {
			int result = 0;
			
			Sql( delegate ( SqlConnection connection ) {
				SqlCommand command = connection.CreateCommand();
				command.CommandText = removeHitsQuery;
				command.Parameters.Add( new SqlParameter( "@AccessionVersion", accessionVersion ) );
				result = command.ExecuteNonQuery();
			} );
			
			return result;
		}
		#endregion
	}
}
